Optimizing ODBC Queries

ODBC performance can be improved by fine-tuning queries according to the recommendations below. By making queries more explicit it can reduce the time needed to gather the information of interest.

Notes

• Some ODBC-capable applications, such as Crystal Reports, request the entirety of the Data Dictionary, even when the query is fully qualified.

• If a database service shuts down during the lifetime of the connection to the CygNet ODBC driver, and the first query against that service occurs while it has shutdown, the error message will indicate that the table was not found. If a query has been successfully executed against that service before the connection is lost, the error message will indicate the CygNet service is not in service. If the service is restarted after a table not found error, the query will still be executed fine.

• The only services that will be available to the current ODBC connection are the services that are in the OK state at the time the connection is made.

Recommendations

• Since the data dictionary is loaded on-demand, if no table owner is specified in the query, the entire data dictionary for every service is loaded, which can slow down the initial run of the query dramatically. To optimize the query, specify the table owner. For example, use select device_id from acme4_dds.dds_dds_device_header instead of select device_id from dds_device_header.
• During initial development of reports or applications in tools like Microsoft Access, use a custom DSN to minimize the amount of time it takes to load the list of tables. When deploying the applications, custom DSN do not need to be used.
• Specify the table_owner column when querying the OA tables for table, column, and index information to avoid loading the data dictionary for all services.
• Specify only the columns you're going to use in your query. This can increase performance of queries against services such as the PNT and FAC which have many columns.
• When performing a join against the pnt_header_record table that includes PointId or PointIdLong, list pointid or pointidlong first in the join criteria, before site and service. This helps the ODBC driver pick a better index.
• When performing a join against the fac_header table that includes facility_id list facility_id first in the join criteria, before facility_site and facility_service. This helps the ODBC driver pick a better index.
• Use Stored Procedures to complete tasks such as:

1. VHS Service Rollup
2. Point in Time
3. Delayed Writes

• Use Indexes as Views to optionally choose exact indexes in order to fine-tune performance. This method provides the following benefits:

1. Eliminate manual intra-service joins where an index already provides that join. This is most visible on the DDS, such as in the C2 and C3 indexes.
2. Provide access to indexes that are sparse, such as the DDS in-progress transaction index and the DDS non-template device index.
3. Provide explicit index selection to improve query performance.
4. Read from the index rather that the data record, since the index records are often smaller than the data records, thereby increasing access time if the data has not already been cached by the service.

Stored Procedures

Stored procedures provide extended CygNet functionality when using ODBC.

VHS Service Rollup

VHS rollup functionality is exposed through a stored procedure called HistoryRollup. Values must be specified for the following parameters in the order they appear in the table:

The returned columns are:

History Rollup Sample

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Option Explicit
 
Dim objConnVhs
Set objConnVhs = WScript.CreateObject("ADODB.Connection")
 
objConnVhs.ConnectionString = "DSN=CygNet;ServiceFilter=*.*"
objConnVhs.Open()
 
Dim objRS
Set objRS = CreateObject("ADODB.RecordSet")
objRS.Open "call mysite_vhs.historyrollup('MYSITE.UIS:3450520367_SPEED', '2024-05-24 07:30:00', '2024-05-24 13:30:00', 'CalcMean', 'Minutes', 30, 0)", objConnVhs
 
objRS.MoveFirst()
 
While Not objRS.EOF
    WScript.Echo "Value: " & objRS.Fields("Value") & ". PointTime: " & objRS.Fields("PointTime")
    objRS.MoveNext()
Wend
 
objConnVhs.Close

Also see History Rollups for more information.

Point in Time

VHS point in time functionality is exposed through a stored procedure called EnableExactTimeRetrieval. This procedure modifies the behavior of retrieval for VHS queries that specify exact pointtime or recordtime values (not ranges). This option is connection specific.

The only parameter is Enable, the accepted values are:

• ON, ENABLE, YES, Y, TRUE, T, or 1 to enable this behavior
• OFF, DISABLE, NO, N, FALSE, F, or 0 to disable this behavior

If EnableExactTimeRetrieval is enabled, only a single value for the given timestamp would be returned. If there are multiple values with the same timestamp only one will be returned. This may not be the desired behavior. To ensure all values with the same timestamp are returned, disable the EnableExactTimeRetrieval feature.

This is analogous to the History Playback feature in CygNet Studio.

By default, the behavior is disabled.

Point in Time Example

1
2
3
4
5
6
7
8
9
10
11
Option Explicit
 
Dim objConnVhs
Set objConnVhs = WScript.CreateObject("ADODB.Connection")
 
objConnVhs.ConnectionString = "DSN=CygNet;ServiceFilter=*.*"
objConnVhs.Open()
 
Dim objRS
Set objRS = CreateObject("ADODB.RecordSet")
objRS.Open "call mysite_vhs.enableexacttimeretrieval('enable')", objConnVhs

Delayed Writes

Delayed writes for bulk updates is exposed through a stored procedure called EnableDelayedWrites. If enabled, data will be visible in the database as they occur but will only be written to the disk at 30 second intervals. This feature reduces the number of writes required for each record update. This option is only effective on queries with multiple updates and all changes will be written to disk at the completion of the query regardless of time interval. It is connection specific. This option is only available to services that have read/write ODBC Access.

The only parameter is Enable, the accepted values are:

• ON, ENABLE, YES, Y, TRUE, T, or 1 to enable this behavior
• OFF, DISABLE, NO, N, FALSE, F, or 0 to disable this behavior

By default, the behavior is disabled.

Delayed Writes Example

1
2
3
4
5
6
7
8
9
10
11
Option Explicit
 
Dim objConn
Set objConn = WScript.CreateObject("ADODB.Connection")
 
objConn.ConnectionString = "DSN=CygNet;ServiceFilter=*.*"
objConn.Open()
 
Dim objRS
Set objRS = CreateObject("ADODB.RecordSet")
objRS.Open "call mysite_pnt.enabledelayedwrites('enable')", objConn

Indexes as Views

Database indexes for the CygNet DBS-based services are exposed as views, which allow query writers to optionally choose exact indexes in order to fine-tune performance.

• Select Search (right-click in the service pane, then select Select Search…) This option is not available for the ACS, DDS, and MSS.
• DBS Tables (right-click on the service in the service hierarchy, select Service Details, then DBS Tables)

Intra-service Joins

Manual intra-service joins can be eliminated where an index already provides that join. This is most visible on the DDS, such as in the dds_c2_index and dds_c3_index tables.

Example

The dds_c2_index includes the Device Category, Device ID, DataGroup Type, Data Group Ordinal, and Data Group Description. The following queries return equivalent results (although not necessarily in the same order):

With indexes as views, use the following query:

Example

The dds_ir_index includes the Remote Device ID and its primary Comm ID.

One way to query:

With indexes as views, the following reduces the complexity of the query:

Sparse Indexes

Index views provide access to indexes that are sparse, such as the DDS in-progress transaction index and the DDS non-template device index.

Example

There are two HyperPoint indexes on the PNT that have an implicit "HyperPointEnabled = 'Y'" condition on them (pnt_h1_index and pnt_h2_index). Doing a full scan of either index only needs to read point data for the points that are actually HyperPoints.

Since CygNet ODBC is unaware of the conditions that are on sparse indexes, the following query will never use that index:

Explicit Index Selection

Including the explicit index selection will improve query performance.

When using a query like this:

index selection is generally poor. The query essentially searches over all points that match site = 'cygdemo' and service = 'uis', but ignores the facilityid criteria.

If the query is changed to use the pnt_s2_index table:

query time and data selection is improved. If you need any other columns from the table, you'll need to perform a join against the pnt_header_record table.

When joining against another table, it is likely that processing the "index" tables first in the join will perform better than if it being processed later. To force the join order, you can use Join Hints to test what is best given the query and the expected data set.

Join Hints

From the v7.0 SQL Engine Programmer's Guide:

The OpenAccess SDK SQL engine syntax includes specification of the preferred processing order as a HINT at the end of the SELECT statement.

• HINT JOIN QUERY ORDER
• HINT JOIN (tablex, tabley, tablez)

Examples

Reading from the Index Record

Since the index records are often smaller than the data records, and if one of the indexes has all of the columns that you are concerned about, and the query is unbounded, reading from the index will incur less disk I/Os if the data has not already been cached by the service.